package org.transfer.service;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.transfer.model.FinanceTemplate;
import org.transfer.model.ImportLog;
import org.transfer.model.RegisterTable;
import org.transfer.utils.ConfigUtil;
import org.transfer.utils.LogUtils;
import org.transfer.utils.database.JDBCMYUtils;

@Component
public class HandlingDataService {

	@Autowired
	private RegisterTableService registerTableService;
	@Autowired
	private ImportLogService importLogService;

	private String logPath = "";

	/**
	 * 数据处理
	 * 
	 * @throws IOException
	 */
	public void handlingData() throws IOException {
		System.out.println("===================处理开始===================");
		String logsFile = ConfigUtil.getConfigure().getProperty("logsFile");
		logPath = logsFile + "handling.log";
		try {
			ImportLog importLog = new ImportLog();
			importLog.setRecoveryState("1");
			importLog.setIp(ConfigUtil.getHostIp());
			List<ImportLog> list = importLogService.findList(importLog);
			for (ImportLog entity : list) {
				if (StringUtils.isBlank(entity.getTransferState())) {
					try {
						if ("1".equals(entity.getBakType())) {
							unMysql(entity);
						} else if ("2".equals(entity.getBakType())) {
							unSqlServer(entity);
						}
						System.out.println(entity.getFileName() + "处理完成！");
						entity.setTransferState("1");
						entity.setTransferMsg("处理完成");
						importLogService.save(entity);
					} catch (Exception e) {
						System.out.println(entity.getFileName() + "处理失败！");
						entity.setTransferState("0");
						entity.setTransferMsg("处理失败");
						importLogService.save(entity);
						e.printStackTrace();
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("===================处理结束===================");
	}

	/**
	 * mysql
	 * 
	 * @param fw
	 * @throws IOException
	 * @throws SQLException
	 */
	public void unMysql(ImportLog importLog) throws IOException, SQLException {
		// mysql
		List<Map<String, Object>> maps;

		RegisterTable registerTable = new RegisterTable();
		registerTable.setAreaCode(importLog.getAreaCode());
		registerTable.setCompanyCode(importLog.getCompanyCode());
		registerTable.setCompanyName(importLog.getCompanyName());
		registerTable = registerTableService.getTable(registerTable);
		registerTableService.deleteFinanceTables(registerTable);
		Connection connection = JDBCMYUtils.getConnectionMy(importLog.getNewDbName());
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		try {
			list = findSysTableSql(importLog, registerTable, "1");
		} catch (Exception e) {
			LogUtils.logs(logPath, "【" + importLog.getFileName() + "】处理失败");
			e.printStackTrace();
		}
		int sum = 0;
		for (Map map : list) {
			String name = String.valueOf(map.get("name"));
			String sql = String.valueOf(map.get("sql"));
			String executeType = String.valueOf(map.get("executeType"));
			if ("1".equals(executeType)) {// 创建
				PreparedStatement preparedStatement = connection.prepareStatement(sql);
				JDBCMYUtils.execUpdate(preparedStatement, new Object[0]);
				/*
				 * Statement statement = connection.createSQLXML()();
				 * statement.execute(sql); statement.close();
				 */
			} else if ("2".equals(executeType)) {// 执行
				CallableStatement callableStatement = connection.prepareCall(sql);
				callableStatement.execute();
				callableStatement.close();
			} else if ("3".equals(executeType)) {// 查询
				PreparedStatement preparedStatement = connection.prepareStatement(sql);
				maps = JDBCMYUtils.execQuery(preparedStatement, new Object[0]);
				if (maps == null || maps.size() <= 0) {
					continue;
				}
				int i = 0;
				try {
					i = doInsert(name, maps);
				} catch (Exception e) {
					e.printStackTrace();
				}
				if (i > 0) {
					LogUtils.logs(logPath, "【" + importLog.getFileName() + "】-【" + name + "】模型处理完成，共" + i + "条数据");
				}
				sum += i;
			}
		}
		if (sum <= 0) {
			LogUtils.logs(logPath, "【" + importLog.getFileName() + "】模型处理失败");
			throw new RuntimeException("处理失败");
		}
		if (list.size() > 0) {
			LogUtils.logs(logPath, "【" + importLog.getFileName() + "】处理完毕，共" + list.size() + "张表，" + sum + "条数据！");
			System.out.println("---------【" + importLog.getFileName() + "】mysql处理完毕，共" + list.size() + "张表，" + sum
					+ "条数据！----------");
		} else {
			LogUtils.logs(logPath, "【" + importLog.getFileName() + "】mysql处理失败，未找到模板！");
			System.out.println("---------【" + importLog.getFileName() + "】mysql处理失败，未找到模板！----------");
		}
		JDBCMYUtils.closeConnection(connection);
	}

	private int doInsert(String name, List<Map<String, Object>> maps) {
		int i = 0;
		List<Map<String, Object>> temp = new ArrayList<>();
		for (int j = 0; j < maps.size(); j++) {
			temp.add(maps.get(j));
			if (temp.size() >= 600 || j == maps.size() - 1) {
				if (name.equals("基础表_财务类_电子账簿信息表")) {
					i += registerTableService.insertAccountBook(temp);
				} else if (name.equals("基础表_财务类_辅助信息表")) {
					i += registerTableService.insertAssist(temp);
				} else if (name.equals("基础表_财务类_辅助余额表")) {
					i += registerTableService.insertAssistBalance(temp);
				} else if (name.equals("基础表_财务类_会计科目表")) {
					i += registerTableService.insertAccount(temp);
				} else if (name.equals("基础表_财务类_记账凭证表")) {
					for (Map<String, Object> m : temp) {
						m.put("记账凭证摘要", (m.get("记账凭证摘要") + "").trim());
					}
					i += registerTableService.insertVoucher(temp);
				} else if (name.equals("基础表_财务类_记账凭证辅助明细表")) {
					i += registerTableService.insertAssistDetail(temp);
				} else if (name.equals("基础表_财务类_科目余额及发生额表")) {
					i += registerTableService.insertAccountBalance(temp);
				}
				temp.clear();
			}
		}
		return i;
	}

	/**
	 * sqlserver
	 * 
	 * @param fw
	 * @throws IOException
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public void unSqlServer(ImportLog importLog) throws IOException, SQLException, ClassNotFoundException {
		// sqlserver
		List<Map<String, Object>> maps;
		RegisterTable registerTable = new RegisterTable();
		registerTable.setAreaCode(importLog.getAreaCode());
		registerTable.setCompanyCode(importLog.getCompanyCode());
		registerTable.setCompanyName(importLog.getCompanyName());
		registerTable = registerTableService.getTable(registerTable);
		registerTableService.deleteFinanceTables(registerTable);
		Connection connection = JDBCMYUtils.getConnectionMs(importLog.getNewDbName());
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		try {
			list = findSysTableSql(importLog, registerTable, "2");
		} catch (Exception e) {
			LogUtils.logs(logPath, "【" + importLog.getFileName() + "】处理失败");
			e.printStackTrace();
		}
		int sum = 0;
		for (Map map : list) {
			String name = String.valueOf(map.get("name"));
			String sql = String.valueOf(map.get("sql"));
			String executeType = String.valueOf(map.get("executeType"));
			System.out.println(name);
			System.out.println(sql);
			try {
				if ("1".equals(executeType)) {// 创建
					Statement statement = connection.createStatement();
					statement.execute(sql);
					statement.close();
				} else if ("2".equals(executeType)) {// 执行
					CallableStatement callableStatement = connection.prepareCall(sql);
					callableStatement.execute();
					callableStatement.close();
				} else if ("3".equals(executeType)) {// 查询
					PreparedStatement preparedStatement = connection.prepareStatement(sql);
					maps = JDBCMYUtils.execQuery(preparedStatement, new Object[0]);
					if (maps == null || maps.size() <= 0) {
						continue;
					}
					int i = 0;
					try {
						i = doInsert(name, maps);
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (i > 0) {
						LogUtils.logs(logPath, "【" + importLog.getFileName() + "】-【" + name + "】模型处理完成，共" + i + "条数据");
					}
					sum += i;
				}
			} catch (Exception e) {
				LogUtils.logs(logPath, "【" + importLog.getFileName() + "】-【" + name + "】模型处理失败");
				e.printStackTrace();
			}
		}
		if (sum <= 0) {
			LogUtils.logs(logPath, "【" + importLog.getFileName() + "】模型处理失败");
			throw new RuntimeException("处理失败");
		}
		if (list.size() > 0) {
			LogUtils.logs(logPath, "【" + importLog.getFileName() + "】处理完毕，共" + list.size() + "张表，" + sum + "条数据！");
			System.out.println("---------【" + importLog.getFileName() + "】sqlsserver处理完毕，共" + list.size() + "张表，" + sum
					+ "条数据！----------");
		} else {
			LogUtils.logs(logPath, "【" + importLog.getFileName() + "】sqlsserver处理失败，未找到模板！");
			System.out.println("---------【" + importLog.getFileName() + "】sqlsserver处理失败，未找到模板！----------");
		}
		JDBCMYUtils.closeConnection(connection);

	}

	/**
	 * 获取系统模板列表
	 * 
	 * @return
	 */
	public List<Map<String, String>> findSysTableSql(ImportLog importLog, RegisterTable registerTable, String dbType) {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		String areaCode = importLog.getAreaCode();
		String companyCode = importLog.getCompanyCode();
		String companyName = importLog.getCompanyName();
		String industry = registerTable.getIndustry();
		String industryCode = getIndustryCode(registerTable.getIndustry());
		String sysName = registerTable.getSysName();
		String dataContent = registerTable.getDataContent();
		List<FinanceTemplate> templateList = registerTableService.findTemplateList(sysName, dbType);
		for (FinanceTemplate template : templateList) {
			String sql = template.getContent().replaceAll("\r", " ").replaceAll("\n", " ");
			sql = sql.replace("${b_xzqh}", areaCode);
			sql = sql.replace("${b_dwdm}", companyCode);
			sql = sql.replace("${b_dwmc}", companyName);
			sql = sql.replace("${b_industryname}", industry);
			sql = sql.replace("${b_industryid}", industryCode);
			sql = sql.replace("&lt;", "<");
			sql = sql.replace("&gt;", ">");
			sql = sql.replace("${b_ztlx}", dataContent);
			Map<String, String> map = new HashMap<String, String>();
			map.put("name", template.getTableName());
			map.put("sql", sql);
			map.put("executeType", template.getExecuteType());
			list.add(map);
		}
		return list;
	}

	/**
	 * 获取行业编码
	 * 
	 * @param industry
	 * @return
	 */
	public String getIndustryCode(String industry) {
		if (industry.equals("党政机关")) {
			return "10001";
		} else if (industry.equals("重点国企")) {
			return "20001";
		} else if (industry.equals("金融机构")) {
			return "30001";
		} else if (industry.equals("医疗机构")) {
			return "40001";
		} else if (industry.equals("学校")) {
			return "50001";
		}
		return "";
	}

}
